<?php
class Model_CupGame extends Zend_Db_Table_Abstract
{
	protected $_name    = 'g1_cup_game';
	protected $_primary = 'id';

	
	public function getGameById($game_id){
		return $this->find($game_id)->current();
	}

	public function generateGames($league_id){
		$model_league = new Model_League();
		$league = $model_league->getLeagueById($league_id);
		
		$count = 1;
		for($count=1;$count<$league->team_count +1;$count++){
			$nb = 1;
			for($nb=1;$nb<$league->team_count+1;$nb++){
				if($count!=$nb){
					$data['league_id']=$league_id;
					$data['home_team_id'] = $count;
					$data['away_team_id'] = $nb;
					
					$this->insert($data);
				}
			}	
		}
	}
	
	public function getAllGamesByLeagueAndUser($league_id,$user_id){
		$select = "select lth.team_name as home_team_name , lta.team_name as away_team_name,
				          g.home_team_score , g.away_team_score,lth.logo as home_logo,lta.logo as away_logo
				   from g1_game g , g1_league_team lth,g1_league_team lta
				   where g.league_id=" . $league_id .
		           " and g.league_id = lth.league_id
		             and g.league_id = lta.league_id
		           	 and g.home_team_id = lth.team_id
		             and g.away_team_id = lta.team_id
		           	 and (g.home_team_id= " . $user_id . " or g.away_team_id = " . $user_id .")";
		
		return $this->_db->fetchAll( $select );
	}
	
	
	public function getFinishedGamesByCupId($cup_id,$group_name){
		$select = "select g.id,lth.team_name as home_team_name , lta.team_name as away_team_name,
				          g.home_team_score , g.away_team_score,g.home_team_confirm ,g.away_team_confirm,
				lth.logo as home_logo,lta.logo as away_logo
				   from g1_cup_game g , g1_cup_team lth,g1_cup_team lta
				   where g.cup_id=" . $cup_id .
					   " and g.cup_id = lth.cup_id
					   	and g.group_name='" . $group_name ."'
		             and g.cup_id = lta.cup_id
		           	 and g.home_team_id = lth.team_id
		             and g.away_team_id = lta.team_id
		           	and g.home_team_confirm = 'yes'
					 and g.away_team_confirm = 'yes'";

	
		return $this->_db->fetchAll( $select );
	}
	
	public function getToConfirmGamesByCupId($cup_id,$group_name){
		$select = "select g.id,lth.team_name as home_team_name , lta.team_name as away_team_name,
				          g.home_team_score , g.away_team_score,g.home_team_confirm ,g.away_team_confirm,
				lth.logo as home_logo,lta.logo as away_logo
				   from g1_cup_game g , g1_cup_team lth,g1_cup_team lta
				   where g.cup_id=" . $cup_id .
					   " and g.cup_id = lth.cup_id
					   	and g.group_name='" . $group_name ."'
		             and g.cup_id = lta.cup_id
		           	 and g.home_team_id = lth.team_id
		             and g.away_team_id = lta.team_id
		           	 and ((g.home_team_confirm = 'yes' and g.away_team_confirm = 'no')or(
					   		g.home_team_confirm = 'no' and g.away_team_confirm = 'yes'))";
	
		return $this->_db->fetchAll( $select );
	}
	
	public function getToStartGamesByCupId($cup_id,$group_name){
		$select = "select g.id,lth.team_name as home_team_name , lta.team_name as away_team_name,
				          g.home_team_score , g.away_team_score,g.home_team_confirm ,g.away_team_confirm,
				lth.logo as home_logo,lta.logo as away_logo
				   from g1_cup_game g , g1_cup_team lth,g1_cup_team lta
				   where g.cup_id=" . $cup_id .
					   " and g.cup_id = lth.cup_id
					   	and g.group_name='" . $group_name ."'
		             and g.cup_id = lta.cup_id
		           	 and g.home_team_id = lth.team_id
		             and g.away_team_id = lta.team_id
		           	 and g.home_team_confirm = 'no'
					 and g.away_team_confirm = 'no'";
	
		return $this->_db->fetchAll( $select );
	}
	
	public function getAllPlayedGamesByCupAndUser($cup_id,$user_id){
		$select = "select lth.team_name as home_team_name , lta.team_name as away_team_name,
				          g.home_team_score , g.away_team_score,lth.logo as home_logo,lta.logo as away_logo
				   from g1_cup_game g , g1_cup_team lth,g1_cup_team lta
				   where g.cup_id=" . $cup_id .
					   " and g.cup_id = lth.cup_id
		             and g.cup_id = lta.cup_id
		           	 and g.home_team_id = lth.team_id
		             and g.away_team_id = lta.team_id
					 and g.home_team_confirm = 'yes' 
					 and g.away_team_confirm = 'yes'
		           	 and (lth.user_id= " . $user_id . " or lta.user_id = " . $user_id .")";
	
		return $this->_db->fetchAll( $select );
	}
	
	public function getAllToConfirmGamesByCupAndUser($cup_id,$user_id){
		$select = "select g.id, lth.team_name as home_team_name , lta.team_name as away_team_name,
				          g.home_team_score , g.away_team_score,lth.logo as home_logo,lta.logo as away_logo
				   from g1_cup_game g , g1_cup_team lth,g1_cup_team lta
				   where g.cup_id=" . $cup_id .
					   " and g.cup_id = lth.cup_id
		             and g.cup_id = lta.cup_id
		           	 and g.home_team_id = lth.team_id
		             and g.away_team_id = lta.team_id
					 and g.home_team_confirm = 'no'
					 and g.away_team_confirm = 'yes'
		           	 and lth.user_id= " . $user_id . 
		           " union select g2.id, lth2.team_name as home_team_name , lta2.team_name as away_team_name,
				          g2.home_team_score , g2.away_team_score ,lth2.logo as home_logo,lta2.logo as away_logo
				   from g1_cup_game g2 , g1_cup_team lth2,g1_cup_team lta2
				   where g2.cup_id=" . $cup_id .
					   " and g2.cup_id = lth2.cup_id
		             and g2.cup_id = lta2.cup_id
		           	 and g2.home_team_id = lth2.team_id
		             and g2.away_team_id = lta2.team_id
					 and g2.home_team_confirm = 'yes'
					 and g2.away_team_confirm = 'no'
		           	 and lta2.user_id= " . $user_id .
		           	 " order by id asc" ;

		return $this->_db->fetchAll( $select );
	}
	
	public function getAllNewGamesByCupAndUser($cup_id,$user_id){
		$select = "select g.id,lth.team_name as home_team_name , lta.team_name as away_team_name,
				          g.home_team_score , g.away_team_score,lth.logo as home_logo,lta.logo as away_logo
				   from g1_cup_game g , g1_cup_team lth,g1_cup_team lta
				   where g.cup_id=" . $cup_id .
					   " and g.cup_id = lth.cup_id
		             and g.cup_id = lta.cup_id
		           	 and g.home_team_id = lth.team_id
		             and g.away_team_id = lta.team_id
					 and g.home_team_confirm = 'no'
					 and g.away_team_confirm = 'no'
		           	 and (lth.user_id= " . $user_id . " or lta.user_id = " . $user_id .")

		           	union
		           	 select g2.id,lth2.team_name as home_team_name , lta2.team_name as away_team_name,
				          g2.home_team_score , g2.away_team_score,lth2.logo as home_logo,lta2.logo as away_logo
				   from g1_cup_game g2 , g1_cup_team lth2,g1_cup_team lta2
				   where g2.cup_id=" . $cup_id .
					   " and g2.cup_id = lth2.cup_id
		             and g2.cup_id = lta2.cup_id
		           	 and g2.home_team_id = lth2.team_id
		             and g2.away_team_id = lta2.team_id
					 and g2.home_team_confirm = 'yes'
					 and g2.away_team_confirm = 'no'
		           	 and lth2.user_id= " . $user_id . " 

		           	union
		           	 	select g3.id,lth3.team_name as home_team_name , lta3.team_name as away_team_name,
				          g3.home_team_score , g3.away_team_score,lth3.logo as home_logo,lta3.logo as away_logo
				   from g1_cup_game g3 , g1_cup_team lth3,g1_cup_team lta3
				   where g3.cup_id=" . $cup_id .
					   " and g3.cup_id = lth3.cup_id
		             and g3.cup_id = lta3.cup_id
		           	 and g3.home_team_id = lth3.team_id
		             and g3.away_team_id = lta3.team_id
					 and g3.home_team_confirm = 'no'
					 and g3.away_team_confirm = 'yes'
		           	 and lta3.user_id = " . $user_id .
		            " order by id asc" ;
	
		return $this->_db->fetchAll( $select );
	}
	
	public function confirmGameByGameId($game_id){
		$data = array('home_team_confirm'  => 'yes',
				       'away_team_confirm' => 'yes');
		$this->update($data, 'id='.$game_id);
	}
	
	public function updateGameScoreByUserId($game_id,$user_id,$away_score,$home_score){
		$game = $this->find($game_id)->current();
		$data = array('home_team_score'  => $home_score,
				'away_team_score' => $away_score);
		
		$model_cup_team = new Model_CupTeam();
		
		if($model_cup_team->getCupTeamInfoByCupIdAndTeamId($game->cup_id,$game->home_team_id)->user_id == $user_id){
			$data['home_team_confirm'] = 'yes';
		}else{
			$data['away_team_confirm'] = 'yes';
		}
	
		$this->update($data, 'id='.$game_id);
       
	}
	
	public function getAllGamesByCupId($cup_id){
	    	$select = $this->select()
    			->where('cup_id=?', $cup_id);
    	$row = $this->fetchAll($select);
    	return $row;
	}
	
	public function deleteGamesByCupIdAndTeamId($cup_id, $team_id){
		$delete = 'DELETE FROM g1_cup_game                         
                        WHERE cup_id='.$cup_id.' and (home_team_id =' .
                           $team_id .' or away_team_id = ' .$team_id .')';
		
		$this->_db->query( $delete );
	}
	
	public function clearGamesByLeagueIdAndTeamId($league_id, $team_id){
		$delete = 'update  g1_game set home_team_score=null ,away_team_score = null , 
				                       home_team_confirm="no",away_team_confirm="no"
                        WHERE league_id='.$league_id.' and (home_team_id =' .
	                        $team_id .' or away_team_id = ' .$team_id .')';
	
		$this->_db->query( $delete );
	}
	
	public function addGamesByLeagueIdAndTeamId($league_id, $team_id){
		$model_league = new Model_League();
		$league = $model_league->getLeagueById($league_id);
		
		$count = 1;
		for($count=0;$count<$league->team_count ;$count++){
			if(($count+1)!=$team_id){
				$data['league_id']=$league_id;
				$data['home_team_id'] = $count +1;
				$data['away_team_id'] = $team_id;
					
				$select = $this->select()
						->from($this, 'count(id) as count')
						->where('league_id = ?', $league_id)
						->where('home_team_id = ?',$count +1)	
						->where('away_team_id = ?',$team_id)	;
				 
				$exist = $this->fetchRow($select);
				
				$dispo = ($exist->count == 0);
				if($dispo){
					$this->insert($data);
				}
				
				
				
				$data['league_id']=$league_id;
				$data['home_team_id'] = $team_id;
				$data['away_team_id'] = $count +1;
					
				$select = $this->select()
						->from($this, 'count(id) as count')
						->where('league_id = ?', $league_id)
						->where('home_team_id = ?',$team_id)	
						->where('away_team_id = ?', $count +1)	;
				 
				$exist = $this->fetchRow($select);
				
				$dispo = ($exist->count == 0);
				if($dispo){
					$this->insert($data);
				}
			}		
		}
	}
	
	public function addGamesByCupIdAndTeamId($cup_id, $team_id){
		$model_cup = new Model_Cup();
		$cup = $model_cup->getCupById($cup_id);
		
		$model_cup_team = new Model_CupTeam();
		$team_group = $model_cup_team->getCupTeamInfoByCupIdAndTeamId($cup_id, $team_id)->group_name;

	
		$group_teams = $model_cup_team->getTeamsByGroup($cup_id,$team_group);

		foreach ($group_teams as $team){
			if($team->team_id!=$team_id){
				if(!empty($team->user_id)){
					$select = $this->select()
					->from($this, 'count(id) as count')
					->where('cup_id = ?', $cup_id)
					->where('home_team_id = ?',$team->team_id)
					->where('away_team_id = ?',$team_id)	;
						
					$exist = $this->fetchRow($select);
					
					$dispo = ($exist->count == 0);
					if($dispo){
						$data['cup_id']=$cup_id;
						$data['home_team_id'] = $team->team_id;
						$data['away_team_id'] = $team_id;
						$data['stage']='group-stage';
						$data['group_name'] = $team_group;
						$this->insert($data);
						
						$data['cup_id']=$cup_id;
						$data['home_team_id'] = $team_id;
						$data['away_team_id'] = $team->team_id;
						$data['stage']='group-stage';
						$data['group_name'] = $team_group;
						$this->insert($data);
					}
				}
			}
		}
		
		
	}
	
	public function getGamesByCupIdAndGroupId($cup_id,$group_name){
		$select = $this->select()
					->where('cup_id=?', $cup_id)
		            ->where('group_name=?',$group_name);
		$rows = $this->fetchAll($select);
		return $rows;
	}
	
	public function updateGameScoreByAdminUser($game_id,$away_score,$home_score){
		$game = $this->find($game_id)->current();
		$data = array('home_team_score'  => $home_score,
				'away_team_score' => $away_score);
	
		$data['home_team_confirm'] = 'yes';
		$data['away_team_confirm'] = 'yes';
	
	
		$this->update($data, 'id='.$game_id);
			
	}
}